﻿using System;
using System.Data.SqlClient;
using System.Data;

namespace DBLag
{
    public class DbConnection
    {
        // Used in both types of connections below
        private const string DB_NAME = "StudentsDB";

        // ------ Local connection SETUP ------
        private const string YOUR_PC_NAME = "jonas-laptop-s";
        private const string YOUR_SQLSERVERS_NAME = "SQLEXPRESS";
        //@ bruges således at compileren ikke tror at \ er en escape character. (gå  tilbage).
        private static readonly string connectionString = @"Server=" + YOUR_PC_NAME + @"\" + YOUR_SQLSERVERS_NAME + ";Initial Catalog=" + DB_NAME + ";Integrated Security=True";

        // ------ Network connection SETUP ------
        //private const string HOST = "";
        //private const string USER_ID = "";
        //private const string PASSWORD = "";
        //private static readonly string connectionString = @"Server=" + HOST + ";Initial Catalog=" + DB_NAME + ";User ID=" + USER_ID + ";Password=" + PASSWORD;

        private static SqlCommand dbCmd;
        private static SqlConnection dbconn;

        private static DbConnection instance = null; // Singleton implementation. Only one instance of the connection class is allowed.

        private DbConnection()
        {
            Console.WriteLine("Connection: {0}", connectionString); // Shows the connection. Usefull for debugging
    
            // Connection to SQL server
            dbconn = new SqlConnection(connectionString);

            Console.WriteLine("Connection timeout: {0}", dbconn.ConnectionTimeout); // Connection info
        }

        public static DbConnection GetInstance()
        {
            if (instance == null)
            {
                instance = new DbConnection();
            }
            return instance;
        }

        // Gets the object of the current SQLConnection.
        public SqlConnection Dbconn
        {
            get { return dbconn; }
        }

        public void OpenConnection()
        {
            try
            {
                dbconn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error opening connection");
                Console.WriteLine(e.Message);
            }
        }

        public void CloseConnection() // Give up ressources
        {
            try
            {
                if (dbconn != null) // Connection must not be null when trying to close it. If something goes wrong when initiating the connection, the object will be null and will generate an exception.
                {
                    dbconn.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error closing connection");
                Console.WriteLine(e.Message);
            }
        }

        public SqlCommand GetDbCommand(string sql)
        {   
            if (dbconn.State.ToString().CompareTo("Open") != 0)
            {
                OpenConnection();
            }
            if (dbCmd == null)
            {
                dbCmd = new SqlCommand(sql, dbconn);
            }
            if (dbCmd.Parameters.Count > 0)
            {
                dbCmd.Parameters.Clear(); // Removes parametres before the object is used again
            }
            dbCmd.CommandText = sql;
            
            return dbCmd;
        }
    }
}
